const db = require('./index')


module.exports = {
    /**
     * 根据分类名称获取分类id
     * @param {*} category_name 
     * @returns 
     */
    getCategoryIdByName: async (category_name) => {
        const sql = `select category_id from category where category_name = ? `;
        const category = await db.query(sql, category_name);
        // [ RowDataPacket { category_id: 1 } ]
        return category[0].category_id;
    },
    /**
     * 获取首页商品
     * @param {*} category_id 
     * @returns 
     */
    getHomeProduct: async (category_id) => {
        const sql = `select * from product where category_id = ? order by product_sales desc limit 7`;
        return await db.query(sql, category_id);
    },
    /**
     * 获取热门商品
     * @param {*} categoryID 
     * @param {*} offset 
     * @param {*} rows 
     * @returns 
     */
    getHotProduct: async (categoryID, offset = 0, rows = 0) => {
        let sql = `select * from product where category_id = ? `;
        for (let i = 0; i < categoryID.length - 1; i++) {
            sql += `or category_id = ? `;
        }
        if (rows != 0) {
            sql += `order by product_sales desc limit ${offset},${rows}`;
        }
        return await db.query(sql, categoryID);

    },
    /**
     * 
     * @returns 获取商品分类信息
     */
    getCategory: async () => {
        const sql = `select * from category`;
        return await db.query(sql, []);
    },
    /**
     * 获取全部商品信息
     * @param {*} offset 
     * @param {*} row 
     * @returns 
     */
    getAllproduct: async (offset = 0, rows = 0) => {
        const sql = ` select * from product limit ${offset}, ${rows}`;
        return await db.query(sql, [])
    },
    /**
     * 获取全部商品个数
     * @returns 
     */
    getAlltotal: async () => {
        const sql = `select count(1) from product`;
        return await db.query(sql, [])
    },
    /**
     * 根据分类获取商品分页
     * @param {*} categoryID 
     * @param {*} offset 
     * @param {*} rows 
     * @returns 
     */
    getProductByCategory: async (categoryID, offset = 0, rows = 0) => {
        const sql = ` select * from product where category_id = ? limit ${offset},${rows}`;
        const sql2 = ` select count(1) from product where category_id = ?`;
        return [await db.query(sql, categoryID), await db.query(sql2, categoryID)]
        // return await db.query(sql,categoryID);
    },
    /**
     * 根据商品id获取商品详情
     * @param {*} productID 
     * @returns 
     */
    getProductDetailsById: async productID => {
        const sql = ` select * from product where product_id = ? `;
        return await db.query(sql, productID);
    },
    /**
     * 根据商品id获取商品图片
     * @param {*} productID 
     * @returns 
     */
    getProductPictureById: async productID => {
        const sql = ` select * from product_picture where product_id = ?`;
        return await db.query(sql, productID)
    },
}